﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class UserInfoDAL
    {
        //负责操作数据库
        //0：代表查询成功      1：代表查询失败
        public int SelectUser(UserInfo userInfo)
        {
            //连接数据库进行比对
            SQLiteConnection sQLiteConnection = null;
            string sql = "select * from UserInfo where username = @uname and password = @pwd";
            SQLiteParameter[] sQLiteParameters = new SQLiteParameter[] { new SQLiteParameter("@uname", userInfo.Username), new SQLiteParameter("@pwd", userInfo.Password) };

            //获取接收的查询结果
            using (SQLiteDataReader sQLiteDataReader = SQLiteHelper.ExecuteReader(out sQLiteConnection, sql, sQLiteParameters))
            {
                if (sQLiteDataReader.Read())
                {
                    userInfo.Uid = sQLiteDataReader.GetInt32(0);
                    userInfo.Username = sQLiteDataReader.GetString(1);
                    userInfo.Password = sQLiteDataReader.GetString(2);
                    userInfo.Realname = sQLiteDataReader.GetString(3);
                    userInfo.Position = sQLiteDataReader.GetString(4);

                    sQLiteConnection.Close();
                    return 0;
                }
                else
                {
                    sQLiteConnection.Close();
                    return 1;
                }
            }
                   
        }

        public DataTable ShowUser()
        {
            string sql = "select * from UserInfo";

            return SQLiteHelper.ExecuteFill(sql);
        }


        public int InsertUser(UserInfo userInfo)
        {
            string sql = "INSERT INTO UserInfo ( username, password, realname, position) VALUES ( @username, @password, @realname, @position)";

            SQLiteParameter[] sQLiteParameter = new SQLiteParameter[]
            {
                new SQLiteParameter("@username", userInfo.Username),
                new SQLiteParameter("@password", userInfo.Password),
                new SQLiteParameter("@realname", userInfo.Realname),
                new SQLiteParameter("@position", userInfo.Position)

            };

            //执行添加操作

            return SQLiteHelper.ExecuteNonQuery(sql, sQLiteParameter);
        }
         

        public int UpdateUser(UserInfo userInfo)
        {
            string sql = "UPDATE UserInfo SET username = @username, password = @password, realname = @realname, position = @position WHERE uid = @uid";

            SQLiteParameter[] sQLiteParameter = new SQLiteParameter[]
            {
                new SQLiteParameter("@username", userInfo.Username),
                new SQLiteParameter("@password", userInfo.Password),
                new SQLiteParameter("@realname", userInfo.Realname),
                new SQLiteParameter("@position", userInfo.Position),
                new SQLiteParameter("@uid", userInfo.Uid)
            };

            //执行添加操作

            return SQLiteHelper.ExecuteNonQuery(sql, sQLiteParameter);
        }

        public int DeleteUser(int uid)
        {
            string sql = "delete from UserInfo where uid = @uid";

            SQLiteParameter[] sQLiteParameter = new SQLiteParameter[]
            {
                new SQLiteParameter("@uid", uid)
            };


            //执行删除操作
            return SQLiteHelper.ExecuteNonQuery(sql, sQLiteParameter);
        }
    }
}
